'''
''' Calls custom stored procedure.
'''
Public Sub CallCustomStoredProcedureToAdd()
'' Step 1: Configure parameters for your stored procedure if the stored procedure accepts parameters.
'' Each database type has different parameter type.
'' Please change them accordingly to fit your application's logic.
'' SQL Server:
'' Parameter type: System.Data.SqlDbType
'' Oracle:
'' Parameter type: System.Data.OracleClient.OracleType
'' MySql:
'' Parameter type: MySql.Data.MySqlClient.MySqlDbType
Dim firstParameter As BaseClasses.Data.StoredProcedureParameter = Nothing
'' For SQL Server: use parameter type System.Data.SqlDbType
firstParameter = New BaseClasses.Data.StoredProcedureParameter("@pk_EmployeeFirstName", "John", System.Data.SqlDbType.VarChar, System.Data.ParameterDirection.Input)
'' For Oracle: use parameter type System.Data.OracleClient.OracleType
'firstParameter = New BaseClasses.Data.StoredProcedureParameter("pk_EmployeeFirstName", "John", System.Data.OracleClient.OracleType.VarChar, System.Data.ParameterDirection.Input)
'' For MySql: use parameter type MySql.Data.MySqlClient.MySqlDbType
'firstParameter = New BaseClasses.Data.StoredProcedureParameter("@pk_EmployeeFirstName", "John", MySql.Data.MySqlClient.MySqlDbType.VarChar, System.Data.ParameterDirection.Input, True)
Dim secondParameter As BaseClasses.Data.StoredProcedureParameter = Nothing
'' For SQL Server: use parameter type System.Data.SqlDbType
secondParameter = New BaseClasses.Data.StoredProcedureParameter("@pk_EmployeeLastName", "Smith", System.Data.SqlDbType.VarChar, System.Data.ParameterDirection.Input)
'' For Oracle: use parameter type System.Data.OracleClient.OracleType
'secondParameter = New BaseClasses.Data.StoredProcedureParameter("pk_EmployeeLastName", "Smith", System.Data.OracleClient.OracleType.VarChar, System.Data.ParameterDirection.Input)
'' For MySql: use parameter type MySql.Data.MySqlClient.MySqlDbType
'secondParameter = New BaseClasses.Data.StoredProcedureParameter("@pk_EmployeeLastName", "Smith", MySql.Data.MySqlClient.MySqlDbType.VarChar, System.Data.ParameterDirection.Input, True)
' Handle the case where the primary key column is of type uniqueidentifier
' with a default value of newid(). In this case the value may be set in
' the stored procedure so we'll need to get the value back via
' an output parameter.
Dim thirdParameter As BaseClasses.Data.StoredProcedureParameter = Nothing
thirdParameter = New BaseClasses.Data.StoredProcedureParameter("@pk_OutputParameter", Nothing, System.Data.SqlDbType.Int, System.Data.ParameterDirection.Output)
'' NOTE: if this parameter has a string type such as varchar, nvarchar, string, etc. you have to set its size:
'' thirdParameter.Size = 10
'' Step 2: Add the configured parameters to an array list.
Dim parameterList(2) As BaseClasses.Data.StoredProcedureParameter
parameterList(0) = firstParameter
parameterList(1) = secondParameter
parameterList(2) = thirdParameter
Dim myStoredProcedure As BaseClasses.Data.StoredProcedure = Nothing
'' "DatabaseNorthwind1" is a connection string obtained from Web.config
'' located in application's root directory.
'' Step 3: Connect to the stored procedure.
myStoredProcedure = New BaseClasses.Data.StoredProcedure("DatabaseNorthwind1", "Stored_Procedure_Name", parameterList)
'' Step 4: Run the stored procedure to insert a new record using the specified values.
'' RunNonQuery() will return true if stored procedure successfully executed. Otherwise, it will return false.
'' Use RunQuery or RunNonQuery. RunQuery is used when a set of records is being returned. RunNonQuery is used when one or more values are returned through output parameters.
If (myStoredProcedure.RunNonQuery()) Then
' Get new primary key value if
' value for primary key column
' will be set in the stored procedure.
Dim outputParameter As System.Data.IDataParameter
For Each outputParameter In myStoredProcedure.OutputParameters
Dim primaryKeyValue As Object = outputParameter.Value
Next
Else
' You can raise an exception in the custom stored procedure and catch the exception and reporting it to the user.
' To raise the exception:
' SET NOCOUNT ON;
' RAISERROR (N'My custom error message goes here', 11, 1)
' IMPORTANT: If you raise an error that has a severity level of 10 or less, it is considered
' a warning, and no exception is raised. The severity of the error must be between 11 and 20
' for an exception to be thrown.
' Once the exception is raised, you can look at:
' myStoredProcedure.ErrorMessage to get the text of the error message and use RegisterJScriptAlert to report this to the user.
End If
End Sub
|